package com.example.bysj.Dao;
//201902104061徐中堂
import com.example.bysj.domain.ProfTitle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
//声明数据库访问类
@Repository("ProfTitleDao")
public class ProfTitleDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //根据id查找
    public ProfTitle getById(int id){
        String sql = "select * from ProfTitle where id=?";
        List<ProfTitle> profTitles = jdbcTemplate.query(sql, new Object[]{id},new RowMapper<ProfTitle>() {
            @Override
            public ProfTitle mapRow(ResultSet rs, int rowNum) throws SQLException {
                ProfTitle profTitle = new ProfTitle();
                profTitle.setId(rs.getInt("id"));
                profTitle.setNo(rs.getString("no"));
                profTitle.setDescription(rs.getString("description"));
                profTitle.setRemarks(rs.getString("remarks"));
                return profTitle;
            }
        });
        if (!profTitles.isEmpty()){
            return profTitles.get(0);
        } else
            return null;
    }
    //查找全部
    public List<ProfTitle> getAll(){
        String sql = "select * from ProfTitle";
        List<ProfTitle> profTitles = jdbcTemplate.query(sql, new RowMapper<ProfTitle>() {
            @Override
            public ProfTitle mapRow(ResultSet rs, int rowNum) throws SQLException {
                ProfTitle profTitle = new ProfTitle();
                profTitle.setId(rs.getInt("id"));
                profTitle.setNo(rs.getString("no"));
                profTitle.setDescription(rs.getString("description"));
                profTitle.setRemarks(rs.getString("remarks"));
                return profTitle;
            }
        });
        return profTitles;
    }
    //添加职称
    public boolean add(List<ProfTitle> profTitles){
        String sql = "INSERT INTO ProfTitle(no,description.remarks) VALUES(?,?,?)";
        for (int i = 0; i<profTitles.size(); i++){
            int rowAffected = jdbcTemplate.update(sql,
                    profTitles.get(i).getNo(),
                    profTitles.get(i).getDescription(),
                    profTitles.get(i).getRemarks());
            if (rowAffected != 1){
                return false;
            }
        }
        return true;
    }
    //根据id删除
    public boolean delete(int id){
        String sql = "delete from ProfTitle where id=?";
        int rowAffected = jdbcTemplate.update(sql,id);
        if (rowAffected!=1){
            return false;
        } else
            return true;
    }
    //根据id，修改对应的职称
    public boolean update(int id,ProfTitle profTitle){
        String sql = "update ProfTitle set no=?,description=?,remarks=? where id=?";
        int rowAffected = jdbcTemplate.update(sql,
                profTitle.getNo(),
                profTitle.getDescription(),
                profTitle.getRemarks(),
                id);
        if (rowAffected!=1){
            return false;
        } else {
            return true;
        }
    }
}
